CREATE DATABASE PassbookViet
GO

USE PassbookViet
GO

CREATE TABLE Locations (	
	ID								INT				IDENTITY (1, 1),		-- PK
	Name							NVARCHAR(256),
	Altitude						FLOAT, 
	Latitude						FLOAT		NOT NULL,
	Longtitude						FLOAT		NOT NULL,
	ReleventText					NVARCHAR(512),	
	PRIMARY KEY (ID)			
);

CREATE TABLE PassType (
	ID								INT				IDENTITY (1,1),
	TeamIdentifier					NVARCHAR(256),		-- StandardKey		-- Considering Duplicate, this is fixed in code. This is the ID that come along with PassID
	PassTypeIdentifier				NVARCHAR(256),		-- StandardKey		-- Considering Duplicate, this is fixed in code. This is the ID that come along with PassID
	PRIMARY KEY (ID),
);

CREATE TABLE PassWebServiceUrl (
	ID								INT				IDENTITY (1,1),
	WebServiceURL					NVARCHAR(256),		-- WebServiceKeys
	PRIMARY KEY (ID)
);

CREATE TABLE Devices (
	ID								INT				IDENTITY (1,1),
	DeviceLibraryIdentifier			VARCHAR(256)	NOT NULL,		-- Receive from user's device
	AuthenticationToken				VARCHAR(16)		NOT NULL,		-- WebServiceKeys	Apple sepectifies this - Generated on server
	PushToken						VARCHAR(256)	NOT NULL,		-- Receive from user's device
	PRIMARY KEY (ID)
);

CREATE TABLE Registration (
	ID								INT				IDENTITY (1,1),
	DeviceID						INT		NOT NULL,
	PassID							INT		NOT NULL,
	PRIMARY KEY (ID)
);

CREATE TABLE ImageSets (
	ID								INT				IDENTITY (1,1),
	Background						VARCHAR(256),		-- URL to background image
	BackgroundRetina				VARCHAR(256),		-- URL to Retina background image
	Icon							VARCHAR(256),		-- URL to icon image
	IconRetina						VARCHAR(256),		-- URL to Retina icon image
	Thumbnail						VARCHAR(256),		-- URL to thumbnail image
	ThumbnailRetina					VARCHAR(256),		-- URL to Retina thumbnail image
	Strip							VARCHAR(256),		-- URL to strip image
	StripRetina						VARCHAR(256),		-- URL to Retina strip image
	PRIMARY KEY (ID)
);

CREATE TABLE Passes (
	ID								INT				IDENTITY (1,1),			-- PK
	SerialNumber					VARCHAR(12)		UNIQUE,	-- StandardKey
	PassTypeID						INT				FOREIGN KEY REFERENCES PassType(ID),
	FormatVersion					INT,				-- StandardKey
	OrganizationName				NVARCHAR(256),		-- StandardKey
	[Description]					NVARCHAR(256),		-- StandardKey	
	WebServiceUrlID					INT				FOREIGN KEY REFERENCES PassWebServiceUrl(ID),
	ImageSetID						INT				FOREIGN KEY REFERENCES ImageSets(ID)
	PRIMARY KEY (ID)
);

CREATE TABLE PassLocation (
	ID								INT				IDENTITY (1,1),
	LocationID						INT				FOREIGN KEY REFERENCES Locations (ID),
	PassID							INT				FOREIGN KEY REFERENCES Passes (ID),
	PRIMARY KEY (ID)
);